﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using yncTech.Common;
using yncTech.BusinessObjects;
using System.Globalization;
using System.Text;

using EntitySpaces.Interfaces;

public partial class Admin_Fin_Fin001b : AdminBasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        lbMessage.Text = "";
        if (!fileUpload.HasFile)
        {
            lbMessage.Text = "请选择要上传的excel文件";
            return;
        }
        string fileName = Guid.NewGuid().ToString() + Path.GetExtension(fileUpload.FileName);

        string file = Server.MapPath("/") + "UploadFile\\SapData\\" + fileName;
        //Session["file"] = file;
        if (File.Exists(file))
        {
            File.Delete(file);
        }
        fileUpload.SaveAs(file);
        var data = ExcelToDataTable("", true, file);
        if (WebHelper.CheckIsNotEmpty(data) && SAP_Data_Validation(data))
        {
            btnSave.Enabled = true;
            Session["data"] = data;
            lbMessage.Text = "文件验证通过，请点击【" + btnSave.Text + "】保存到系统中！"; ;
        }
        else
        {
            File.Delete(file);
            lbMessage.Text = failmsg.ToString();
        }
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        lbMessage.Text = "";
        var upLoadData = Session["data"] as DataTable;
        string currentUser = LoginUserInfo.CurrentUser.LoginName;
        DateTime currentDT = DateTime.Now;
        DateTimeFormatInfo format = new DateTimeFormatInfo();

        DateTime tmpDate;

        format.ShortDatePattern = "yyyy/MM/dd";

        using (esTransactionScope scope = new esTransactionScope())
        {
            try
            {

                for (int i = 0; i < upLoadData.Rows.Count; i++)
                {
                    FinReceipt fr = new FinReceipt();
                    fr.IsSAP = true;
                    fr.IsConfirmed = false;

                    fr.LogicalSystem = upLoadData.Rows[i][0].ToString();
                    fr.CompanyCode = upLoadData.Rows[i][1].ToString();
                    fr.DocumentsType = upLoadData.Rows[i][2].ToString();
                    fr.DocumentsNo = upLoadData.Rows[i][3].ToString();
                    fr.Reference = upLoadData.Rows[i][4].ToString();
                    fr.DocumentHeaderText = upLoadData.Rows[i][5].ToString();
                    fr.ReferenceKey = upLoadData.Rows[i][6].ToString();
                    fr.RevesedBy = upLoadData.Rows[i][7].ToString();
                    fr.ReferenceTransact = upLoadData.Rows[i][8].ToString();
                    DateTime.TryParse(upLoadData.Rows[i][9].ToString(), format, DateTimeStyles.None, out tmpDate);
                    fr.DocumentDate = tmpDate;
                    DateTime.TryParse(upLoadData.Rows[i][10].ToString(), format, DateTimeStyles.None, out tmpDate);
                    fr.PostingDate = tmpDate;
                    fr.UserName = upLoadData.Rows[i][11].ToString();
                    fr.FirstStorageDate = DateTime.Now;


                    fr.UpdatedBy = fr.CreatedBy = currentUser;
                    fr.UpdatedDttm = fr.CreatedDttm = currentDT;
                    fr.Save();
                }
                scope.Complete();

                WebHelper.CloseDialog(true);
            }
            catch (Exception ex)
            {
                lbMessage.Text = ex.Message ;

            }
        }

    }
    protected void btnClose_Click(object sender, EventArgs e)
    {
        Session.Remove("data");
        WebHelper.CloseDialog(true);
    }
    private IWorkbook workbook = null;
    const string FAIL_DUPLICATE_TEMPLATE = "第{0}行，{1}:在文件中重复.";
    const string FAIL_DUPLICATE_DB_TEMPLATE = "第{0}行，{1}:已存在数据库中.";
    const string FAIL_EMPTY_TEMPLATE = "第{0}行，{1}:不能为空.<br/>";
    const string FAIL_FORMAT_TEMPLATE = "第{0}行，{1}:数据格式不正确.<br/>";
    string[] columnName = new string[] { "Logical System", "Company Code", "Document Type", "Document Number", "Reference", "Document Header Text", "Reference Key", "Reversed with", "Reference Transact.", "Document Date", "Posting Date", "User name" };
    /// <summary>
    /// 将excel中的数据导入到DataTable中
    /// </summary>
    /// <param name="sheetName">excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
    {
        ISheet sheet = null;
        DataTable data = new DataTable();
        int startRow = 0;

        try
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn("c" + i.ToString());
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i < rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
        }
        catch (Exception ex)
        {
            //Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }
    StringBuilder failmsg = new StringBuilder();
    /// <summary>
    /// validation the data
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    bool SAP_Data_Validation(DataTable data)
    {
        failmsg = new StringBuilder();
        bool retValue = true;

        DateTimeFormatInfo format = new DateTimeFormatInfo();
        format.ShortDatePattern = "yyyy/MM/dd";

        DateTime dateCheck;
        List<string> docNoList = new List<string>();
        for (int i = 0; i < data.Rows.Count; i++)
        {
            string emptyCol = string.Empty;
            string formatCol = string.Empty;
            bool isExists = docNoList.Exists(r => r == data.Rows[i][3].ToString());
            if (isExists)
            {
                failmsg.AppendFormat(FAIL_DUPLICATE_TEMPLATE, i + 1, emptyCol.TrimEnd(',') + "-" + data.Rows[i][1].ToString());
            }
            //else
            //{

            //    FinReceipt fr = new FinReceipt();
            //    fr.Query.Where(fr.Query.DocumentsNo == data.Rows[i][1].ToString().Trim());
            //    fr.Query.Load();
            //    if (fr.es.HasData)
            //    {
            //        failmsg.AppendFormat(FAIL_DUPLICATE_DB_TEMPLATE, i + 1, emptyCol.TrimEnd(',') + "-" + data.Rows[i][1].ToString());

            //    }
            //    else
            //    {
            //        docNoList.Add(data.Rows[i][1].ToString());
            //    }

            //}
            for (int j = 0; j < 11; j++)
            {
                string value = data.Rows[i][j].ToString();
                if (string.IsNullOrEmpty(value) && (j == 1 || j == 2 || j == 3 || j == 8 || j == 9 || j == 10 || j == 11))
                {
                    emptyCol = columnName[j] + ",";
                }
                else
                {
                    if (j == 9 || j == 10)
                    {
                        if (!DateTime.TryParse(value, format, DateTimeStyles.None, out dateCheck))
                        {
                            formatCol = columnName[j] + ",";
                        }
                    }
                    //else if (j == 12)
                    //{
                    //    if (!value.Equals("S1") || !value.Equals("S2") || !value.Equals("S3") || !value.Equals("S4") || !value.Equals("S5") || !value.Equals("S6"))
                    //    {
                    //        formatCol = columnName[j] + ",";
                    //    }
                    //}
                }
            }
            if (emptyCol.Length > 0)
            {
                failmsg.AppendFormat(FAIL_EMPTY_TEMPLATE, i + 1, emptyCol.TrimEnd(','));
            }
            if (formatCol.Length > 0)
            {
                failmsg.AppendFormat(FAIL_FORMAT_TEMPLATE, i + 1, formatCol.TrimEnd(','));
            }
        }

        if (failmsg.Length > 0)
        {
            retValue = false;
        }


        return retValue;
    }
}